package com.iflysse.dao;


import com.iflysse.pojo.po.common.CommonPoResp;
import com.iflysse.pojo.po.req.StudentInTeachingClassPoReq;
import com.iflysse.pojo.po.req.TeachingClassPoReq;
import com.iflysse.pojo.po.req.UpdateAmountOfStudentPoReq;
import com.iflysse.pojo.po.resp.TeachingClassNamePoResp;
import com.iflysse.pojo.po.resp.TeachingClassPoResp;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.List;
@Repository
public interface TeachingClassDao {
    /**
     * 添加教学班级
     * @param teachingClassPoReq
     * @return
     */
    @Insert({"insert into tb_teaching_class (teaching_class_id,teacher_id,teaching_class_name,course_id) values(UUID(),#{teacherId},#{teachingClassName},#{courseId})" })
    @Options(useGeneratedKeys = true, keyProperty = "teaching_class_id")
    int addTeachingClass(TeachingClassPoReq teachingClassPoReq) throws SQLException;

    /**
     * 根据教学班级编号删除教学班级,采用假删除,更改状态
     * @param teachingClassId
     * @return
     */
    @Update("update tb_teaching_class set is_delete = 1 where teaching_class_id = #{teachingClassId} ")
    int deleteTeachingClassByTeachingClassId(String teachingClassId) throws SQLException;

    /**
     * 根据老师编号获取该老师的所有教学班级
     * @param teacherId
     * @return
     */
    @Select({"select * from tb_teaching_class  where teacher_id = #{teacherId} and is_delete = 0"})
    @Results({
            @Result(property = "teachingClassID",column = "teaching_class_id"),
            @Result(property = "studentAmount",column = "student_amount"),
            @Result(property = "courseID",column = "course_id"),
            @Result(property = "teachingClassName",column = "teaching_class_name"),
    })
    List<TeachingClassPoResp> getAllTeachingClassesByTeacherId(String teacherId) throws SQLException;


    /**
     * 根据教学班级名称获取教学班级信息
     * @param teachingClassName 教学班级名称
     * @return
     */
    @Select({"select * from tb_teaching_class  where teaching_class_name = #{teachingClassName} and is_delete = 0"})
    @Results({
            @Result(property = "teachingClassId",column = "teaching_class_id"),
            @Result(property = "studentAmount",column = "student_amount"),
            @Result(property = "courseId",column = "course_id"),
            @Result(property = "teachingClassName",column = "teaching_class_name"),
    })
    TeachingClassPoResp getTeachingClassByClassName(String teachingClassName) throws SQLException;


    /**
     * 将学生添加到教学班级
     * @param studentInTeachingClassPoReq
     * @return
     */
    @Insert({
            "<script>"+
            "insert into tb_teaching_class_student (teaching_class_student_id,teaching_class_id,student_id) values "+
            "<foreach collection='studentIdList' item='item' index='index' separator=','>"+
            "(uuid(),#{teachingClassId},#{item})"+
            "</foreach>"+
            "</script>"})
    @Options(useGeneratedKeys = true, keyProperty = "teaching_class_student_id")
    int addStudentToTeachingClass(StudentInTeachingClassPoReq studentInTeachingClassPoReq) throws SQLException;

    /**
     * 将学生从教学班级中移除(批量)
     * @param deleteStudentIdListPoReq
     * @return
     */
    @Update("<script>" +
            "update tb_teaching_class_student set is_delete=1 where teaching_class_id = #{teachingClassId} and student_id in " +
            "<foreach collection='studentIdList' open='(' item='studentId' separator=',' close=')'> #{studentId}</foreach>" +
            "</script>")
    int deleteStudentFromTeachingClass(StudentInTeachingClassPoReq deleteStudentIdListPoReq) throws SQLException;


    /**
     * 获取系统中所有的教学班级名称
     * @return
     */
    @Select({"select * from tb_teaching_class  where  is_delete = 0"})
    @Results({
            @Result(property = "teachingClassId",column = "teaching_class_id"),
            @Result(property = "teachingClassName",column = "teaching_class_name"),
    })
    List<TeachingClassNamePoResp> getAllTeachingClassName() throws SQLException;

    /**
     * 根据教学班级编号获取教学班级
     * @param teachingClassId
     * @return
     */
    @Select({"select * from tb_teaching_class  where teaching_class_id = #{teachingClassId} and is_delete = 0"})
    @Results({
            @Result(property = "teachingClassID",column = "teaching_class_id"),
            @Result(property = "studentAmount",column = "student_amount"),
            @Result(property = "courseID",column = "course_id"),
            @Result(property = "teachingClassName",column = "teaching_class_name"),
    })
    TeachingClassPoResp getTeachingClassByTeachingClassId(String teachingClassId) throws SQLException;


    /**
     * 更新教学班级的名称和课程(若当前教学班级没有产生学习数据)
     * @param teachingClassPoReq
     * @return
     */
    @Update(
            "<script>"+
            "update tb_teaching_class set teaching_class_name = #{teachingClassName} "+
            "<if test='courseId != null and courseId !=&apos;&apos;'> "+
            ", course_id = #{courseId}"+
            "</if>" +
            "where teaching_class_id = #{teachingClassId}" +
            "</script>")

    int updateTeachingClass(TeachingClassPoReq teachingClassPoReq) throws SQLException;

    /**
     * 更新教学班级的学生人数
     * @param updateAmountOfStudentPoReq
     * @return
     */
    @Update("update tb_teaching_class set student_amount = #{studentAmount} where teaching_class_id = #{teachingClassId} ")
    int updateTeachingClassAmountOfStudent(UpdateAmountOfStudentPoReq updateAmountOfStudentPoReq) throws SQLException;

    /**
     * 根据班级获取教学班级人数
     * @param teachingClassId
     * @return
     */
    @Select({"select count(*) from tb_teaching_class_student where teaching_class_id = #{teachingClassId} and is_delete = 0"})
    int getStudentAmountByTeachingClassId(String teachingClassId) throws SQLException;

    /**
     * 根据学生查询所在学习的教学班级编号列表
     * @param studentId
     * @return
     */
    @Select({"select teaching_class_id from tb_teaching_class_student where student_id = #{studentId} and is_delete = 0"})
    List<String> getTeachingClassIdsByStudentId(String studentId) throws SQLException;
    /**
     * 筛选过滤掉已经删除的班级
     * @return
     */
    @Select({" <script>" +
            "select teaching_class_id from tb_teaching_class where is_delete =0 and teaching_class_id in " +
            " <foreach collection='teachingClassIdList' open='(' item='teachingClassId' separator=',' close=')'> #{teachingClassId}</foreach> "+
            " </script>" })
    List<String> getTeachingClassIdsByStudentId2(List<String> teachingClassIdList) throws SQLException;

    /**
     * 根据教学班级编号获取课程编号
     * @param teachingClassId
     * @return
     */
    @Select({"select course_id from tb_teaching_class where teaching_class_id = #{teachingClassId}"})
    String getCourseIdByTeachingClassId(String teachingClassId) throws SQLException;



}
